package com.bf.electroplating;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class ElectroplatingPointSQLGenerator {

    public static void main(String[] args) {
        String filePath = "D:\\北孚科技记录\\各项目开发记录\\主导产业全产业链数字化体系建设项目\\电镀点位.txt"; // 替换为您的文件路径

        try {
            List<String> sqlStatements = convertTextToSQL(filePath);

            // 输出生成的SQL语句
            for (String sql : sqlStatements) {
                System.out.println(sql);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static List<String> convertTextToSQL(String filePath) throws IOException {
        List<String> sqlStatements = new ArrayList<>();
        BufferedReader reader = new BufferedReader(new FileReader(filePath));

        String line;
        String currentCompany = null;
        List<String> currentCoordinates = new ArrayList<>();
        int companyOrder = 1;
        int globalPointCounter = 1; // 全局点位计数器

        while ((line = reader.readLine()) != null) {
            line = line.trim();

            if (line.isEmpty()) {
                continue;
            }

            if (isCoordinateLine(line)) {
                // 坐标行
                currentCoordinates.add(line);
            } else {
                // 公司名称行 - 处理之前公司的所有坐标点
                if (currentCompany != null && !currentCoordinates.isEmpty()) {
                    List<String> companySQLs = generateCompanyPointSQLs(currentCompany, currentCoordinates, 
                            companyOrder++, globalPointCounter);
                    sqlStatements.addAll(companySQLs);
                    globalPointCounter += currentCoordinates.size(); // 更新全局计数器
                }

                // 开始新的公司
                currentCompany = line;
                currentCoordinates = new ArrayList<>();
            }
        }

        // 处理最后一个公司
        if (currentCompany != null && !currentCoordinates.isEmpty()) {
            List<String> companySQLs = generateCompanyPointSQLs(currentCompany, currentCoordinates, 
                    companyOrder, globalPointCounter);
            sqlStatements.addAll(companySQLs);
        }

        reader.close();
        return sqlStatements;
    }

    private static boolean isCoordinateLine(String line) {
        return line.matches("^\\d+\\.\\d+,\\s*\\d+\\.\\d+$");
    }

    private static List<String> generateCompanyPointSQLs(String companyName, List<String> coordinates, 
                                                          int companyOrder, int globalPointCounter) {
        List<String> sqlList = new ArrayList<>();
        String companyCode = generateCompanyCode(companyName, companyOrder);
        String companyId = generateUUID();

        for (int i = 0; i < coordinates.size(); i++) {
            String[] coordParts = coordinates.get(i).split(",\\s*");
            if (coordParts.length == 2) {
                String longitude = coordParts[0].trim();
                String latitude = coordParts[1].trim();

                // 全局排序号：基于坐标在文本中的顺序
                int globalSort = globalPointCounter + i;
                
                String sql = generateSinglePointSQL(companyName, companyCode, companyId,
                        longitude, latitude, i + 1, globalSort);
                sqlList.add(sql);
            }
        }

        return sqlList;
    }

    private static String generateSinglePointSQL(String companyName, String companyCode,
                                                 String companyId, String longitude,
                                                 String latitude, int pointIndex, int globalSort) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO electroplating.`electroplating_point` (");
        sql.append("ID, POINT_CODE, POINT_NAME, COMPANY_ID, COMPANY_CODE, ");
        sql.append("COMPANY_NAME, LONGITUDE, LATITUDE, POINT_TYPE, ");
        sql.append("USAGE_STATUS, CREATE_TIME, DEL_FLAG, SORT");
        sql.append(") VALUES (");

        // ID
        sql.append("'").append(generateUUID()).append("', ");

        // POINT_CODE (企业编码+序号)
        sql.append("'").append(companyCode).append("_P").append(String.format("%02d", pointIndex)).append("', ");

        // POINT_NAME (企业名称+点位+序号)
        sql.append("'").append(companyName).append("点位").append(pointIndex).append("', ");

        // COMPANY_ID
        sql.append("'").append(companyId).append("', ");

        // COMPANY_CODE
        sql.append("'").append(companyCode).append("', ");

        // COMPANY_NAME
        sql.append("'").append(escapeSQL(companyName)).append("', ");

        // LONGITUDE
        sql.append(longitude).append(", ");

        // LATITUDE
        sql.append(latitude).append(", ");

        // POINT_TYPE (默认为01：电镀槽)
        sql.append("'01', ");

        // USAGE_STATUS (默认为1：在用)
        sql.append("'1', ");

        // CREATE_TIME
        sql.append("NOW(), ");

        // DEL_FLAG
        sql.append("'0', ");

        // SORT (全局排序号)
        sql.append(globalSort);

        sql.append(");");

        return sql.toString();
    }

    private static String generateCompanyCode(String companyName, int order) {
        // 简化的企业编码生成逻辑，您可以根据需要调整
        String prefix = "EP";
        String nameAbbr = getCompanyAbbreviation(companyName);
        return prefix + String.format("%02d", order) + "_" + nameAbbr;
    }

    private static String getCompanyAbbreviation(String companyName) {
        // 提取企业名称的首字母或缩写
        if (companyName.length() <= 4) {
            return companyName;
        }

        // 简单的中文转拼音缩写逻辑（实际使用时可能需要更复杂的处理）
        StringBuilder abbr = new StringBuilder();
        for (char c : companyName.toCharArray()) {
            if (Character.isLetter(c)) {
                abbr.append(c);
            } else if (Character.isIdeographic(c)) {
                // 中文取首字（实际应使用拼音转换库）
                abbr.append(c);
                break;
            }
        }

        return abbr.length() > 0 ? abbr.toString() : "COMP";
    }

    private static String generateUUID() {
        return UUID.randomUUID().toString().replace("-", "").substring(0, 32);
    }

    private static String escapeSQL(String value) {
        return value.replace("'", "''");
    }
}
